package org.anyline.simple.mysql;

import org.anyline.adapter.DataReader;
import org.anyline.data.adapter.DataReaderFactory;
import org.anyline.data.jdbc.adapter.JDBCAdapter;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.run.Run;
import org.anyline.entity.*;
import org.anyline.entity.geometry.*;
import org.anyline.metadata.*;
import org.anyline.metadata.type.DatabaseType;
import org.anyline.metadata.type.init.StandardTypeMetadata;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;
import org.anyline.util.BasicUtil;
import org.anyline.util.ConfigTable;
import org.anyline.util.LogUtil;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class PartitionTest {
    private Logger log = LoggerFactory.getLogger(PartitionTest.class);
    @Autowired
    private AnylineService service          ;
    @Autowired
    private JdbcTemplate jdbc               ;
    private Catalog catalog = null          ; //
    private Schema schema   = null          ; //相当于数据库名  查数据库列表 是用SHOW SCHEMAS 但JDBC con.getCatalog()返回数据库名 而con.getSchema()返回null
    private String table    = "crm_user"    ; // 表名


    /**
     * 分区
     * PARTITION BY RANGE(REG_TIME)
     * (
     * FROM ("2000-11-01") TO ("2021-11-01") INTERVAL 1 YEAR,
     * FROM ("2021-11-01") TO ("2022-11-01") INTERVAL 1 MONTH,
     * FROM ("2022-11-01") TO ("2023-01-03") INTERVAL 1 WEEK,
     * FROM ("2023-01-03") TO ("2023-01-11") INTERVAL 1 DAY
     * )
     * @throws Exception 异常
     */
    @Test
    public void part_from_to() throws Exception{
        Table table = head();
        Table.Partition partition = new Table.Partition();
        partition.addColumn("REG_TIME");
        partition.setType(Table.Partition.TYPE.RANGE);
        partition.addSlice(new Table.Partition.Slice().setMin("2000-11-01").setMax("2021-11-01").setInterval(1).setUnit("YEAR"));
        partition.addSlice(new Table.Partition.Slice().setMin("2021-11-02").setMax("2022-11-01").setInterval(1).setUnit("MONTH"));
        partition.addSlice(new Table.Partition.Slice().setMin("2022-11-02").setMax("2023-11-01").setInterval(1).setUnit("WEEK"));
        partition.addSlice(new Table.Partition.Slice().setMin("2023-11-02").setMax("2025-01-11").setInterval(1).setUnit("DAY"));
        table.setPartition(partition);

        service.ddl().create(table);
    }

    /**
     * 分区
     *
     * PARTITION BY RANGE(ID)(
     *  FROM (1) TO (100) INTERVAL 10
     * )
     * @throws Exception 异常
     */
    @Test
    public void partition_from_to_interval() throws Exception{
        Table table = head();
        Table.Partition partition = new Table.Partition();
        partition.addColumn("ID");
        partition.setType(Table.Partition.TYPE.RANGE);
        partition.addSlice(new Table.Partition.Slice().setMin("1").setMax("100").setInterval(10));
        table.setPartition(partition);
        service.ddl().create(table);
    }

    /**
     * 分区
     * PARTITION BY RANGE(ID,QTY)
     * ( PARTITION s1 VALUES LESS THAN (100,100)
     * , PARTITION s2 VALUES LESS THAN (200,200))
     * @throws Exception 异常
     */
    @Test
    public void partition_less() throws Exception{
        ConfigTable.IS_LOG_ADAPTER_MATCH = true;
        Table table = head();
        Table.Partition partition = new Table.Partition();
        partition.addColumn("ID");//.addColumn("QTY");
        partition.setType(Table.Partition.TYPE.RANGE);
        partition.addSlice(new Table.Partition.Slice().setName("s1").setLess("ID",100));//.setLess("QTY",100));
        partition.addSlice(new Table.Partition.Slice().setName("s2").setLess("ID",200));//.setLess("QTY",200));
        partition.addSlice(new Table.Partition.Slice().setName("s3").setLess("ID",300));//.setLess("QTY",1000));
        table.setPartition(partition);
        service.ddl().create(table);
    }


    /**
     * 分区
     * PARTITION BY LIST(ID)
     * ( PARTITION s1 VALUES IN(1,2)
     * , PARTITION s2 VALUES IN(11,12)
     * )
     * @throws Exception 异常
     */
    @Test
    public void partition_list() throws Exception{
        Table table = head();
        Table.Partition partition = new Table.Partition();
        partition.addColumn("ID");
        partition.setType(Table.Partition.TYPE.LIST);
        partition.addSlice(new Table.Partition.Slice().setName("s1").addValues(0).addValues(99));
        partition.addSlice(new Table.Partition.Slice().setName("s2").addValues(100).addValues(999));
        partition.addSlice(new Table.Partition.Slice().setName("s3").addValues(1000).addValues(9999));
        table.setPartition(partition);
        service.ddl().create(table);
    }
    /**
     * 分区
     * PARTITION BY LIST(ID)
     * ( PARTITION s1 VALUES IN(1,2)
     * , PARTITION s2 VALUES IN(11,12)
     * )
     * @throws Exception 异常
     */
    @Test
    public void partition_hash() throws Exception{
        //不支持hash
        Table table = head();
        Table.Partition partition = new Table.Partition();
        partition.addColumn("ID");
        partition.setType(Table.Partition.TYPE.HASH).setModulus(100);
        table.setPartition(partition);
        service.ddl().create(table);
    }

    public Table head() throws Exception{

        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != table){
            service.ddl().drop(table);
        }
        //也可以直接删除(需要数据库支持 IF EXISTS)
        service.ddl().drop(new Table(catalog, schema, this.table));

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNull(table);

        //定义表结构
        table = new Table(this.table);
        table.setComment("表备注");
        //设置分桶方式 DISTRIBUTED BY HASH('ID'） BUCKETS 2
        table.setDistribution(Table.Distribution.TYPE.HASH, 3, "ID");

        table.addColumn("ID", "INT", false, null).setComment("主键");//.autoIncrement(true).primary(true);
        table.addColumn("QTY", "INT").setComment("数量");
        table.addColumn("CODE", "VARCHAR(10)").setComment("编号");
        table.addColumn("CODE2", "double(10)").setComment("编号");
        table.addColumn("NAME", "VARCHAR(20)").setComment("名称");
        table.addColumn("REG_TIME", "date").setComment("注册时间");
        table.addColumn("REG_TIME1", "datetime").setComment("注册时间");
        table.addColumn("REG_TIME2", "timestamp(6)").setComment("注册时间");
        table.addColumn("DATA_VERSION", "double").setComment("数据版本");
        return table;
    }
}
